﻿use housing 
go

/*getSubsidyMoneychange*/
if exists(select 1 from sysobjects where xtype='P' and name='getSubsidyMoneychange') drop proc getSubsidyMoneychange
go
Create proc getSubsidyMoneychange
(
	@applyid  int ,
	@EffectDate int,
	@nowmoney float,
	@moneychange float output
)
as 
begin 
	declare @old float,@noid varchar(10)
	select @noid=apply_noid from Apply where apply_id=@applyid
	select top 1 @old=adj_nowmoney  from SubsidyRecord where adj_noid=@noid and EffectDate<@EffectDate
	order by EffectDate desc
	set @moneychange=@nowmoney-isnull(@old,0)

End
GO


/*getReduceChange*/
if exists(select 1 from sysobjects where xtype='P' and name='getReduceChange') drop proc getReduceChange
go
Create proc getReduceChange
(
	@applyid  int ,
	@EffectDate int,
	@nowmoney float,
	@moneychange float output
)
as 
begin 
	declare @old float
	select top 1 @old=adj_nowreduce  from ReduceRecord where applyid=@applyid and EffectDate<@EffectDate
	order by EffectDate desc
	set @moneychange=@nowmoney-isnull(@old,0)

End
GO

/*getReduceChange*/
if exists(select 1 from sysobjects where xtype='P' and name='getKindRecordUseChange') drop proc getKindRecordUseChange
go
Create proc getKindRecordUseChange
(
	@applyid  int ,
	@EffectDate int,
	@nowusearea float,
	@nowuseareaChange float output
)
as 
begin 
	declare @old float
	select top 1 @old=adj_nowusearea  from KindRecord where applyid=@applyid and EffectDate<@EffectDate
	order by EffectDate desc
	set @nowuseareaChange=@nowusearea-isnull(@old,0)

End
GO







/*GetApplyById*/
if exists(select 1 from sysobjects where xtype='P' and name='GetApplyById') drop proc GetApplyById
go
Create proc GetApplyById
(
	@applyid  int
)
as 
begin 
SELECT [apply_id]
      ,[apply_noid]
      ,[apply_name]
      ,[apply_address]
      ,[apply_phone]
      ,[apply_checkstate]
      ,[apply_time]
      ,[apply_passed]
      ,[apply_street]
      ,[apply_yearincome]
      ,[apply_peoplenum]
      ,[apply_avgincome]
      ,[apply_totlearea]
      ,[apply_avgarea]
      ,[apply_totleproperty]
      ,[apply_paybycard]
      ,[apply_docname]
      ,[apply_docnum]
      ,[apply_docdate]
      ,[apply_longid]
      ,[apply_updchecked]
      ,[apply_familyNum]
,AddSubsidyNum
,CutSubsidyNum
      ,[AddTime]
      ,[EditTime]
      ,[ReduceMoney]
      ,[SubsidyMoney]
      ,[EditNote]
      ,[isLengthenAYear]
      ,[Apply].[effectDate]
      ,[isReduce]
      ,[isSubsidy]
      ,[isKind]
    	,[app_id]
      ,[app_applyid]
      ,[app_noid]
      ,[app_title]
      ,[app_name]
      ,[app_marryed]
      ,[app_workunit]
      ,[app_idcard]
      ,[app_income]
      ,[app_ispro]
  FROM [Apply],Applicant where   [apply_id]=@applyid  and app_title='申请人'

End
GO


--exec GetApplyById 1799

/*DeleteApplyAndAllRef*/
if exists(select 1 from sysobjects where xtype='P' and name='DeleteApplyAndAllRef') drop proc DeleteApplyAndAllRef
go
Create proc DeleteApplyAndAllRef
(	@applyid  int 
)
as 
begin 
--		declare @apply_noid int 
--		declare @EffectDate int 
--		Select @apply_noid=apply_noid ,@EffectDate =EffectDate from Apply where apply_id=@applyid
		delete from apply where apply_id=@applyid 
		delete from Applicant where app_applyid=@applyid 
		delete from Housing where  hou_applyid=@applyid  
		delete from Property where pro_applyid=@applyid 
		delete from ReduceRecord where applyid=@applyid  
		delete from SubsidyRecord where  applyid=@applyid 
		delete from KindRecord where  applyid=@applyid 

End
GO

/*DeleteApplyAndAllRef*/
if exists(select 1 from sysobjects where xtype='P' and name='DeleteApplyByNoidEffectDate') drop proc DeleteApplyByNoidEffectDate
go
Create proc DeleteApplyByNoidEffectDate
(	@apply_noid  int ,
	@EffectDate int 
)
as 
begin 
 
		delete from apply where apply_noid=@apply_noid and  EffectDate =@EffectDate
		delete from Applicant where app_noid=@apply_noid and  EffectDate =@EffectDate
		delete from Housing where hou_noid=@apply_noid and  EffectDate =@EffectDate
		delete from Property where pro_noid=@apply_noid and  EffectDate =@EffectDate
		delete from ReduceRecord where adj_noid=@apply_noid and  EffectDate =@EffectDate
		delete from SubsidyRecord where  adj_noid=@apply_noid and  EffectDate =@EffectDate
		delete from KindRecord where  adj_noid=@apply_noid and  EffectDate =@EffectDate

End
GO



/*函数-上个月*/
if exists(select * from sysobjects where  name='getLastMonth' and xtype='FN' ) drop function getLastMonth
go
Create function getLastMonth(@yearmonth int)
returns int
as 
begin 
	declare @LastMonth int
	if(right(Cast(@yearmonth as varchar(10)),2)='01')
		set @LastMonth=@yearmonth-100+11
	else
		set @LastMonth=@yearmonth-1
	return @LastMonth
end 

----select dbo.getLastMonth(200901)